#! /usr/bin/env python
#coding=utf-8

import docbase
import MySQLdb

class Doc(docbase.DocBase):
    "实现Mysql的Doc"

    docname = 'DocMysql'

    def open_conn(self):
        try:
            self.conn = MySQLdb.connect(host=self.user.config.dbhost,
                                        user=self.user.config.dbuser,
                                        passwd=self.user.config.dbpassword,
                                        db=self.user.config.dbname,
                                        charset='utf8')
        except Exception, e:
            self.user.log.writeLog('mysql connect fail. ' + str(e), 'fatal')
            return None
        
        # test connection
        '''cursor = self.conn.cursor()
        cursor.execute('set names utf8')
        cursor.execute('SELECT * FROM record')
        rs = cursor.fetchall()
        for i in rs:
            print i'''
    
    def add(self, params):
        paramlist = params.items()
        sql = 'INSERT INTO record('
        f = False
        for i, j in paramlist:
            if f:
                sql += ','
            else:
                f = True
            sql += i
            
        sql += ') VALUES('
        f = False
        for i, j in paramlist:
            if f:
                sql += ','
            else:
                f = True
            sql += '\'' + str(j) + '\''
        sql += ')'
        try:
            cursor = self.conn.cursor()
            cursor.execute('set names utf8')
        except:
            print self.user.log.writeLog('insert into db error sql: %s' % sql, 'error')
        return cursor.execute(sql)

    def delete(self, params):
        # params是一个int的list,里面为待删除的记录
        sql = 'DELETE FROM record WHERE '
        f = False
        if not params:
            return None
        for i in params:
            if not f:
                sql += ' r_id=%s ' % (i)
                f = True
            else:
                sql += ' or r_id=%s ' % (i)
        try:
            cursor = self.conn.cursor()
            cursor.execute('set names utf8')
            return cursor.execute(sql)
        except Exception, e:
            self.user.log.writeLog('delete record error: %s sql: %s' % 
                                   (str(e), str(sql)), 'error')
            return None

    def change(self, params):
        pass

    def select(self, params):
        "查询记录条目"
        l = []
        leveldict = {1:'计划内',2:'计划外',3:'未达到计划',4:'超出计划',5:'未预料'}
        try:
            cursor = self.conn.cursor()
            cursor.execute('set names utf8')
            cursor.execute('SELECT r_id,u_id,r_type,FROM_UNIXTIME(r_datetime) as r_datetime,r_amount,b_name,r_description,r_level FROM record ORDER BY r_datetime DESC, r_id DESC')
            rs = cursor.fetchall()
            for i in rs:
                tl = []
                tl.append(('记录ID', i[0]))
                tl.append(('用户名', i[1]))
                tl.append(('记录类型', '支出' if i[2] == '2' else '收入'))
                tl.append(('时间', i[3]))
                tl.append(('金额', i[4]))
                tl.append(('名称', i[5]))
                tl.append(('描述', i[6]))
                tl.append(('级别', leveldict.get(i[7])))
                l.append(tl)
        except Exception, e:
            self.user.log.writeLog('select db error: %s' % (str(e)), 'error')
            return None
        
        return l

    def free(self, params):
        pass
    
    def close_conn(self):
        "关闭数据库连接"
        self.conn.close()
        
